﻿//************************************************************************************************************
//文件名称(File Name)：EstimationGuestMana.cs
//功能描述(Description)：标准客户资产评估
//数据表(Tables)：
//作者(Author)：
//日期(Create Date)：20130222
//参考文档(Reference)(可选)：
//引用(Using) (可选)﹕
//************************************************************************************************************

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using DbAdapter.sqldbAdapter;

namespace AnalyseCrmMana
{


    public class CustomerViewMana
    {

        private string _guestno="";
        private string _guestname="";
        private decimal _perdayasset =0;
  
        //----客户信息---
        private string _khsc = "0";
        private string _khsex = "男";
        private string _khage = "1";
        private string _khQuDao = "DX";
        private string _khBiaoShi = "个人";
        private string _khState = "正常";
        private string _CERTIFICATENUM = "";

        private string _EDUCATIONAL = "初中";
        private string _KHCPTJ = "大华基金";
        private string _ZCGSPM = "8880";



      
        private string _SALESDEPARTMENT = "营业部";
        private string _RELTELPHONE = "";

        private string _KHEMAIL = "";
        private string _KHRELADDRESS = "";
   
        



        /// <summary>
        /// 客户编号
        /// </summary>
        public string guestno
        {
            set{ _guestno=value;}
			get{return _guestno;}
            //get;
            //set;
        }

        /// <summary>
        /// 客户名称
        /// </summary>
        public string guestname
        {
            set{ _guestname=value;}
			get{return _guestname;}
            //get;
            //set;
        }

        /// <summary>
        /// 日均资产
        /// </summary>
        public decimal perdayasset
        {
            set{ _perdayasset=value;}
			get{return _perdayasset;}

            //get;
            //set;
        }
        /// <summary>
        /// 日均资产
        /// </summary>
        public string khsc
        {
            set { _khsc = value; }
            get { return _khsc; }

            //get;
            //set;
        }
        /// <summary>
        /// 日均资产
        /// </summary>
        public string khsex
        {
            set { _khsex = value; }
            get { return _khsex; }

            //get;
            //set;
        }

               
        /// <summary>
        /// 日均资产
        /// </summary>
        public string khage
        {
            set { _khage = value; }
            get { return _khage; }

            //get;
            //set;
        }

                /// <summary>
        /// 日均资产
        /// </summary>
        public string  khQuDao
        {
            set { _khQuDao = value; }
            get { return _khQuDao; }

            //get;
            //set;
        }

         /// <summary>
        /// 日均资产
        /// </summary>
        public string  khBiaoShi
        {
            set { _khBiaoShi = value; }
            get { return _khBiaoShi; }

            //get;
            //set;
        }

                 /// <summary>
        /// 日均资产
        /// </summary>
        public string  khState
        {
            set { _khState = value; }
            get { return _khState; }

            //get;
            //set;
        }

        
                 /// <summary>
        /// 日均资产
        /// </summary>
        public string  CERTIFICATENUM
        {
            set { _CERTIFICATENUM = value; }
            get { return _CERTIFICATENUM; }

            //get;
            //set;
        }

        
                 /// <summary>
        /// 日均资产
        /// </summary>
        public string  EDUCATIONAL
        {
            set { _EDUCATIONAL = value; }
            get { return _EDUCATIONAL; }

            //get;
            //set;
        }

                 /// <summary>
        /// 日均资产
        /// </summary>
        public string KHCPTJ
        {
            set { _KHCPTJ = value; }
            get { return _KHCPTJ; }

            //get;
            //set;
        }
        public string ZCGSPM
        {
            set { _ZCGSPM = value; }
            get { return _ZCGSPM; }

            //get;
            //set;
        }

        public string SALESDEPARTMENT
        {
            set { _SALESDEPARTMENT = value; }
            get { return _SALESDEPARTMENT; }

            //get;
            //set;
        }

        public string RELTELPHONE
        {
            set { _RELTELPHONE = value; }
            get { return _RELTELPHONE; }

            //get;
            //set;
        }

        public string KHEMAIL
        {
            set { _KHEMAIL = value; }
            get { return _KHEMAIL; }

            //get;
            //set;
        }
        public string KHRELADDRESS
        {
            set { _KHRELADDRESS = value; }
            get { return _KHRELADDRESS; }

            //get;
            //set;
        }

          //     }

        /// <summary>
        /// 获取客户信息
        /// </summary>
        /// <param name="usermana"></param>
        /// <returns></returns>
        public IList<CustomerViewMana> GetGustomerInfo(string[] Vpara)
        {

            int startrow = Convert.ToInt32(Vpara[2]) * (Convert.ToInt32(Vpara[1]) - 1);
            int endrow = Convert.ToInt32(Vpara[2]) * Convert.ToInt32(Vpara[1]) + 1;
            string sql = "SELECT B.* FROM "
                        + "(select ROW_NUMBER() OVER(ORDER BY A.GUESTNO DESC) rownumber,GUESTNO,GUESTNAME,RELTELPHONE from Tbt_GuestInfo A) B "
                        + "WHERE B.rownumber >" + startrow + " and B.rownumber < " + endrow;
            sqldb db = new sqldb();
            DataSet ds = db.ExecuteDataset(sqlconn.GetConnStr(), CommandType.Text, sql);

            IList<CustomerViewMana> custlist = null;
            if (ds != null && ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
            {
                custlist = new List<CustomerViewMana>();
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    CustomerViewMana custom = new CustomerViewMana();
                    custom.guestno = dr["GUESTNO"].ToString();
                    custom.guestname = dr["GUESTNAME"].ToString();
                    custom.RELTELPHONE = dr["RELTELPHONE"].ToString();
                    custom.perdayasset = 3030.00M;
                    custlist.Add(custom);
                }
            }
            return custlist;
        }

        /// <summary>
        /// 获取客户信息
        /// </summary>
        /// <param name="usermana"></param>
        /// <returns></returns>
        public IList<CustomerViewMana> GetGustomerInfoForContion(string[] Vpara, string strFilter)
        {
            int startrow = Convert.ToInt32(Vpara[2]) * (Convert.ToInt32(Vpara[1]) - 1);
            int endrow = Convert.ToInt32(Vpara[2]) * Convert.ToInt32(Vpara[1]) + 1;
            string[] strWhere;
            String _strWhere = "";
            if (strFilter != "")
            {
                strWhere = strFilter.Split('|');
                if (strWhere.Length > 0)
                {
                    for (int i = 0; i < strWhere.Length; i++)
                    {
                        _strWhere = _strWhere + " AND " + strWhere[i];
                    }


                }
            }
        
            string sql = "SELECT B.* FROM "
                        + "(select ROW_NUMBER() OVER(ORDER BY A.GUESTNO DESC) rownumber,GUESTNO,GUESTNAME,CERTIFICATENUM,RELTELPHONE from Tbt_GuestInfo A WHERE 1=1 " + _strWhere + ") B "
                        + "WHERE B.rownumber >" + startrow + " and B.rownumber < " + endrow;
            sqldb db = new sqldb();
            DataSet ds = db.ExecuteDataset(sqlconn.GetConnStr(), CommandType.Text, sql);

            IList<CustomerViewMana> custlist = null;
            if (ds != null && ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
            {
                custlist = new List<CustomerViewMana>();
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    CustomerViewMana custom = new CustomerViewMana();
                    custom.guestno = dr["GUESTNO"].ToString();
                    custom.guestname = dr["GUESTNAME"].ToString();
                    custom.RELTELPHONE = dr["RELTELPHONE"].ToString();
                    custom.perdayasset = 3030.00M;
                    custlist.Add(custom);



                }
            }
            return custlist;
        }

        /// <summary>
        /// 获取客户信息
        /// </summary>
        /// <param name="usermana"></param>
        /// <returns></returns>
        public IList<CustomerViewMana> GetGustomerInfoFoPer(string strcusno)
        {


            string sql = " SELECT DATEDIFF(month ,OPENINGDATE,GETDATE()) AS KHSC," +
 "SEX AS KHSEX," +
 " 'DX' AS QUDAO," +
 "DATEDIFF(YEAR ,BIRTHDAY,GETDATE()) AS KHAGE," +
 "'个人' AS BIAOshi," +
             " '正常'  AS KHSTATE," +
 " '大华基金' AS KHCPTJ," +
 "'888' AS ZCGSPM,* ";
            sql = sql + "  from Tbt_GuestInfo   WHERE GUESTNO='" + strcusno + "'";

            sqldb db = new sqldb();
            DataSet ds = db.ExecuteDataset(sqlconn.GetConnStr(), CommandType.Text, sql);
           
            IList<CustomerViewMana> custlist = null;
            if (ds != null && ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
            {
                custlist = new List<CustomerViewMana>();
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    CustomerViewMana custom = new CustomerViewMana();

                    custom.guestno = dr["GUESTNO"].ToString();
                    custom.guestname = dr["GUESTNAME"].ToString();

                    custom.khsc = dr["KHSC"].ToString();
                    custom.khsex = dr["SEX"].ToString();

                    custom.khage = dr["KHAGE"].ToString();
                    custom.khBiaoShi = dr["BIAOshi"].ToString();

                    custom.khState = dr["KHSTATE"].ToString();
                    custom.KHCPTJ = dr["KHCPTJ"].ToString();
                    custom.ZCGSPM = dr["ZCGSPM"].ToString();

                    custom.CERTIFICATENUM = dr["CERTIFICATENUM"].ToString();
                    custom.SALESDEPARTMENT = dr["SALESDEPARTMENT"].ToString();
                    custom.RELTELPHONE = dr["RELTELPHONE"].ToString();
                    custom.KHEMAIL = dr["EMAIL"].ToString();

                    custom.KHRELADDRESS = dr["RELADDRESS"].ToString();
                    custom.EDUCATIONAL = dr["EDUCATIONAL"].ToString();
                    custom.perdayasset = 3030.00M;
                    custlist.Add(custom);
                }
            }
            return custlist;
        }

        /// <summary>
        /// 获取客户数量
        /// </summary>
        /// <param name="username"></param>
        /// <returns></returns>
        public int GetCustomerCount(string username, string strFilter)
        {
            String _strWhere = "";
            string[] strWhere;
            if (strFilter != "")
            {
                strWhere = strFilter.Split('|');

                if (strWhere.Length > 0)
                {
                    for (int i = 0; i < strWhere.Length; i++)
                    {
                        _strWhere = _strWhere + " AND " + strWhere[i];
                    }


                }
            }
            string sql = "select count(1) from Tbt_GuestInfo WHERE 1=1 " + _strWhere;
            sqldb db = new sqldb();
            object rowcount = db.ExecuteScalar(sqlconn.GetConnStr(), CommandType.Text, sql);
            return Convert.ToInt32(rowcount);
        }
    }
}
